package cc.sunni.neti;

import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.HashMap;

/**
 * @author jl
 * @since 2021/2/15 20:00
 */
public class BaiduMapDemo {

    public static void main(String[] args) {
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("address", "北京市海淀区上地十街10号");
        paramMap.put("ak", "AVn7G3Wf9dj1f3HKPVAoGuw1eufazq14");
        paramMap.put("output", "json");

        String result = HttpUtil.get("http://api.map.baidu.com/geocoding/v3", paramMap);
        JSON json = JSONUtil.parse(result);
        // {"status":0,"result":{"location":{"lng":116.3084202915042,"lat":40.05703033345938},"precise":1,"confidence":80,"comprehension":100,"level":"门址"}}
        String lng = json.getByPath("result.location.lng", String.class);
        String lat = json.getByPath("result.location.lat", String.class);
        System.out.println("lng:" + lng);
        System.out.println("lat:" + lat);

        try {
            DriverManager.registerDriver(new Driver());
            String url = "jdbc:mysql://127.0.0.1:3306/neti?serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "123456";
            Connection con = DriverManager.getConnection(url, username, password);
            String sql = "SELECT\n" +
                    "  id\n" +
                    "FROM\n" +
                    "  (SELECT\n" +
                    "    w.id,\n" +
                    "    st_distance (\n" +
                    "      POINT (?, ?),\n" +
                    "      POINT (w.lng, w.lat)\n" +
                    "    ) * 111195 / 1000 AS distance\n" +
                    "  FROM\n" +
                    "    `t_warehouse` w\n" +
                    "    JOIN `t_warehouse_sku` ws\n" +
                    "      ON w.id = ws.`warehouse_id`\n" +
                    "  WHERE ws.`sku_id` = ?\n" +
                    "    AND ws.`num` >= ?\n" +
                    "  ORDER BY distance\n" +
                    "  LIMIT 1) t";
            PreparedStatement ps = con.prepareStatement(sql);
            // 用户收货地址经纬度
            ps.setObject(1, lng);
            ps.setObject(2, lat);
            // skuId
            ps.setObject(3, 1);
            // 购买数量
            ps.setObject(4, 5);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                System.out.println(id);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
